package com.topisv.tms.tools;

import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.antlr.stringtemplate.StringTemplate;
import org.antlr.stringtemplate.StringTemplateGroup;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * Excel导出，多数据源多sheet，使用StringTemplate
 */
public class MultiDataSourceExcelStUtil {
    private static final Logger logger = LoggerFactory.getLogger(MultiDataSourceExcelStUtil.class);
    
    // 每个sheet行数60000
    private static int MAX_ROW_NUM = 60000;
    
    
    /**
     * 把数据输出到OutputStream
     * 
     * @param writer PrintWriter
     * @param stGroup 模板
     * @param sheetNamePre sheet名称前缀
     * @param pageTitle Excel标题
     * @param titles Excel每列的标题
     * @param dataList 待写入的数据
     */
    private static void export(PrintWriter writer, StringTemplateGroup stGroup, String sheetNamePre, String pageTitle, String[] titles, List<Object[]> dataList) {
        logger.debug("begin to export to single excel datasource");
        long startTime = System.currentTimeMillis();

        //计算sheet数量
        int totalRowNum = dataList.size();
        int sheetNum = totalRowNum / MAX_ROW_NUM + (totalRowNum % MAX_ROW_NUM == 0 ? 0 : 1);

        int columnLength = titles.length;

        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            Worksheet worksheet = new MultiDataSourceExcelStUtil.Worksheet();
            worksheet.setPageTitle(pageTitle);
            worksheet.setSheet(sheetNamePre + (sheetNum > 1 ? " - " + (sheetIndex + 1) : ""));
            worksheet.setTitles(titles);
            worksheet.setMergeAcross(columnLength - 1);
            worksheet.setColumnNum(columnLength);
            worksheet.setRowNum(MAX_ROW_NUM + 2);

            StringTemplate body = stGroup.getInstanceOf("st/excel/body");
            List<Row> rowList = new ArrayList<Row>(MAX_ROW_NUM);
            
            int startIndex = sheetIndex * MAX_ROW_NUM;
            int endIndex = Math.min((sheetIndex + 1) * MAX_ROW_NUM - 1, totalRowNum - 1);
            
            for (int rowIndex = startIndex; rowIndex <= endIndex; rowIndex++) {
                Row row = new MultiDataSourceExcelStUtil.Row();
                List<String> result = new ArrayList<String>(columnLength);
                
                Object[] values = dataList.get(rowIndex);
                for (int columnIndex = 0; columnIndex < columnLength; columnIndex++) {
                    Object value = values[columnIndex];
                    
                    if (value == null) {
                        result.add("");
                    } else {
                        if (value instanceof Date) {
                            result.add(DateFormatUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss"));
                        } else {
                            result.add(value.toString().replace("<", "&lt;").replace(">", "&gt;")); //转义，否则excel打开报错。wps打开时报错行以下所有记录不显示 add 20160624
                        }
                    }
                }
                
                row.setResult(result);
                rowList.add(row);
            }
            
            worksheet.setRows(rowList);
            body.setAttribute("worksheet", worksheet);
            
            rowList = null;
            worksheet = null;
            
            writer.print(body.toString());
            
            rowList = null;
            worksheet = null;
            body = null;
            
            logger.debug("生成Excel第{}个sheet.", sheetIndex + 1);
        }

        long endTime = System.currentTimeMillis();
        logger.debug("end to export to single excel datasource. 耗时: {}", (endTime - startTime));
    }

    /**
     * 把数据输出到OutputStream
     * 
     * @param outputStream 输出流
     * @param multiSheetNamePreList sheet名称前缀
     * @param multiPageTitleList Excel标题
     * @param multiTitleList Excel每列的标题
     * @param multiDataList 待写入的数据
     */
    public static void exportMulit(OutputStream outputStream, List<String> multiSheetNamePreList, List<String> multiPageTitleList, List<String[]> multiTitleList, List<List<Object[]>> multiDataList) {
        // 参数校验
        if (multiSheetNamePreList == null || multiPageTitleList == null || multiTitleList == null || multiDataList == null) {
            throw new RuntimeException("params list (sheetNamePres or pageTitles or titles or dataList) must not be null! pelease check.");
        }
        
        if ((multiSheetNamePreList.size() + multiPageTitleList.size() + multiTitleList.size() + multiDataList.size()) != (multiSheetNamePreList.size() * 4) 
                || multiSheetNamePreList.size() == 0) {
            throw new RuntimeException("params list (sheetNamePres and pageTitles and titles and dataList) must have a same size and gt 0! pelease check.");
        }

        StringTemplateGroup stGroup = new StringTemplateGroup("multiExcelTemplate");
        stGroup.setFileCharEncoding("UTF-8");
        
        // 写入excel文件头部信息
        StringTemplate head = stGroup.getInstanceOf("st/excel/head");
        head.setAttribute("createdTime", DateFormatUtils.format(new Date(), "yyyy-MM-dd'T'HH:mm:ss'Z'"));
        
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(outputStream));
        writer.print(head.toString());
        
        //循环每个数据源，构建excel
        for (int i = 0; i < multiSheetNamePreList.size(); i++) {
            String sheetNamePre = multiSheetNamePreList.get(i);
            String pageTitle = multiPageTitleList.get(i);
            String[] titles = multiTitleList.get(i);
            List<Object[]> dataList = multiDataList.get(i);
            
            export(writer, stGroup, sheetNamePre, pageTitle, titles, dataList);
        }
        
        // 写入excel文件尾部
        writer.print("</Workbook>");
        writer.flush();
        writer.close();
    }

    public static void main(String[] args) throws UnsupportedEncodingException, IOException {
        List<String> multiSheetNamePreList = new ArrayList<String>(2);
        List<String> multiPageTitleList = new ArrayList<String>(2);
        List<String[]> multiTitleList = new ArrayList<String[]>(2);
        List<List<Object[]>> multiDataList = new ArrayList<List<Object[]>>(2);
        
        
        //第一类sheet：快递清单明细列表
        multiSheetNamePreList.add("明细");
        multiPageTitleList.add("快递清单明细列表");
        multiTitleList.add(new String[]{ "交接时间", "快递公司", "快递单号", "仓库", "交接人" });
        
        List<Object[]> dataList = new ArrayList<Object[]>();
        multiDataList.add(dataList);

        for (int i = 0; i < 60000 * 1 + 10; i++) {
            dataList.add(new Object[] {new Date(), "天天快递" + (i + 1), "9889258131" + (i + 1), "杭州仓" + (i + 1),  "测试用户" + (i + 1) });
        }

        //第二类sheet：快递清单汇总
        multiSheetNamePreList.add("汇总");
        multiPageTitleList.add("快递清单汇总");
        multiTitleList.add(new String[]{ "快递公司", "仓库", "快递单数"});
        
        List<Object[]> dataList2 = new ArrayList<Object[]>();
        multiDataList.add(dataList2);
        
        for (int i = 0; i < 60 * 1; i++) {
            dataList2.add(new Object[] {"天天快递" + (i + 1), "杭州仓" + (i + 1), (i + 1) });
        }
        
        long t1 = System.currentTimeMillis();
        
        // 写入到本地文件
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        exportMulit(byteArrayOutputStream, multiSheetNamePreList, multiPageTitleList, multiTitleList, multiDataList);

        File file = new File("D:\\\\快递清单.xls");
        OutputStream output = new FileOutputStream(file);
        BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);
        bufferedOutput.write(byteArrayOutputStream.toString().getBytes("UTF-8"));
        bufferedOutput.flush();
        bufferedOutput.close();
        byteArrayOutputStream.close();
        
        long t2 = System.currentTimeMillis();
        
        logger.info("耗时: {}''", t2 - t1);
    }
    
    
    
    
    
    static class Row {
        private List<String> result;

        public List<String> getResult() {
            return result;
        }

        public void setResult(List<String> result) {
            this.result = result;
        }
    }

    static class Worksheet {
        private String pageTitle; //sheet标题
        private String sheet; //sheet名称
        private String[] titles; //每列标题
        private List<Row> rows; //数据行集合
        private int columnNum; //列数
        private int mergeAcross; //标题行合并行数
        private int rowNum; //行数
        

        public String getSheet() {
            return sheet;
        }

        public void setSheet(String sheet) {
            this.sheet = sheet;
        }

        public List<Row> getRows() {
            return rows;
        }

        public void setRows(List<Row> rows) {
            this.rows = rows;
        }

        public int getColumnNum() {
            return columnNum;
        }

        public void setColumnNum(int columnNum) {
            this.columnNum = columnNum;
        }

        public int getRowNum() {
            return rowNum;
        }

        public void setRowNum(int rowNum) {
            this.rowNum = rowNum;
        }

        public String[] getTitles() {
            return titles;
        }

        public void setTitles(String[] titles) {
            this.titles = titles;
        }

        public String getPageTitle() {
            return pageTitle;
        }

        public void setPageTitle(String pageTitle) {
            this.pageTitle = pageTitle;
        }

        public int getMergeAcross() {
            return mergeAcross;
        }

        public void setMergeAcross(int mergeAcross) {
            this.mergeAcross = mergeAcross;
        }

    }

}
